package sample;

import javafx.event.Event;
import javafx.fxml.FXML;
import javafx.scene.control.Label;
import javafx.scene.control.TextArea;
import javafx.stage.DirectoryChooser;

import java.io.File;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

public class Controller {

    @FXML
    private Label fileLabel;
    @FXML
    private TextArea textArea1;
    //文件选择器
    final DirectoryChooser directoryChooser = new DirectoryChooser();
    //目标文件件
    private File file;
    //引文
    String ywSql = "SELECT count(1) as sum from yw";
    //基金项目
    String jjxmSql = "SELECT count(1) as sum from jjxm";
    //作者简介
    String zzjjSql = "SELECT count(1) as sum from zz where OLDZZJJ is not null";
    //作者单位 SELECT count(1) as sum
    //FROM ZZ where OLDDWYS is not null
    String zzSql = "SELECT count(1) as sum from zz where OLDDWYS is not null";

    @FXML
    public void exeButClickedHandle(Event event) {
        if (null == this.file || !this.file.isDirectory()) {
            SBTFxDialog.showConfirmDialog(null, "请选择目录 ", "【错误提示】");
            return;
        }
        File[] files = this.file.listFiles();
        Map<String,Long> totalMap = new HashMap<String,Long>();
        totalMap.put("ywTotal", 0L);
        totalMap.put("jjxmTotal", 0L);
        totalMap.put("zzjjTotal", 0L);
        totalMap.put("zzdwTotal", 0L);
        totalMap.put("total", 0L);
        this.summary(files, totalMap);
        String memo = String.format("%s * 0.022 + %s * 0.022 + %s / 4 * 0.022 + %s / 4 * 0.022 = ",
                totalMap.get("ywTotal"), totalMap.get("zzjjTotal"), totalMap.get("zzdwTotal"), totalMap.get("jjxmTotal"));
        BigDecimal amount = BigDecimal.valueOf(totalMap.get("ywTotal")).multiply(BigDecimal.valueOf(0.022d))
                .add(BigDecimal.valueOf(totalMap.get("zzjjTotal")).multiply(BigDecimal.valueOf(0.022d)))
                .add(BigDecimal.valueOf(totalMap.get("zzdwTotal")).divide(BigDecimal.valueOf(4L)).multiply(BigDecimal.valueOf(0.022d)))
                .add(BigDecimal.valueOf(totalMap.get("jjxmTotal")).divide(BigDecimal.valueOf(4L)).multiply(BigDecimal.valueOf(0.022d)));
        //计件工资制(含税价格引文0.022元/条、基金0.022元/4条、作者机构0.022元/4条、作者简介0.022元/条)
        String message = "------------------------计件工资制（含税价格）--------------------------------\n" +
                "引文0.022元/条、基金0.022元/4条、作者机构0.022元/4条、作者简介0.022元/条 \n" +
                "-------------------------本数统计-------------------------------\n" +
                "共有本数 -> " + totalMap.get("total") + " 本; \n" +
                "-------------------------条数统计-------------------------------\n" +
                "共有引文： -> " + totalMap.get("ywTotal") + " 条记录;\n" +
                "共有作者简介： -> " + totalMap.get("zzjjTotal") + " 条记录;\n" +
                "共有作者单位： -> " + totalMap.get("zzdwTotal") + " 条记录;\n" +
                "共有基金： -> " + totalMap.get("jjxmTotal") + " 条记录;\n" +
                "-------------------------计算金额-------------------------------\n" +
                "计算方式：" + memo + amount.setScale(4, BigDecimal.ROUND_HALF_UP).toString();
//        SBTFxDialog.showConfirmDialog(null,
//                message,
//                "【统计完成】");
        this.textArea1.setText(message);
    }

    //递归 查询目录下所子目录下 mdb文件
    private void summary(File[] files, Map<String, Long> totalMap) {
        for (int i = 0; i < files.length; i++) {
            Statement statement = null;
            try {
                if (files[i].isDirectory()) {
                    summary(files[i].listFiles(),totalMap);
                }
                String filePath = files[i].getPath();
                if (!filePath.endsWith(".MDB") && !filePath.endsWith(".mdb")) {
                    continue;
                }
                totalMap.put("total",totalMap.get("total")+1L);
                statement = DbTool.getStatement(filePath);
                //
                ResultSet ywRs = statement.executeQuery(ywSql);
                if (ywRs != null && ywRs.next()) {
                    totalMap.put("ywTotal",totalMap.get("ywTotal")+ywRs.getLong("sum"));
                }
                ywRs.close();
                //基金
                ResultSet jjxmRs = statement.executeQuery(jjxmSql);
                if (jjxmRs != null && jjxmRs.next()) {
                    totalMap.put("jjxmTotal",totalMap.get("jjxmTotal")+jjxmRs.getLong("sum"));
                }
                jjxmRs.close();
                //
                ResultSet zzjjRs = statement.executeQuery(zzjjSql);
                if (zzjjRs != null && zzjjRs.next()) {
                    totalMap.put("zzjjTotal",totalMap.get("zzjjTotal")+zzjjRs.getLong("sum"));
                }
                zzjjRs.close();
                //
                ResultSet zzdwRs = statement.executeQuery(zzSql);
                if (zzdwRs != null && zzdwRs.next()) {
                    totalMap.put("zzdwTotal",totalMap.get("zzdwTotal")+zzdwRs.getLong("sum"));
                }
                zzdwRs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DbTool.close(null, statement, null);
            }
        }
    }


    @FXML
    public void selectFileHandle(Event event) {
        this.file = directoryChooser.showDialog(null);
        if (null == this.file) {
            this.fileLabel.setText("请选择mdb文件所在目录......");
            return ;
        }
        String path = this.file.getPath();//选择的文件夹路径
        if (null != path) {
            this.fileLabel.setText(file.getPath());
        }
    }
}
